<?php
use Migrations\AbstractMigration;

class POCOR7004 extends AbstractMigration
{
    /**
     * Change Method.
     *
     * More information on this method is available here:
     * http://docs.phinx.org/en/latest/migrations.html#the-change-method
     * @return void
     */
    public function up()
    {
        // Backup table
        $this->execute('CREATE TABLE `zz_7004_report_queries` LIKE `report_queries`');
        $this->execute('INSERT INTO `zz_7004_report_queries` SELECT * FROM `report_queries`');


        // CREATE summary tables and INSERT new rows into report_queries table
        $this->execute('CREATE TABLE IF NOT EXISTS `summary_institutions`( `academic_period_id` int(11) NOT NULL, `academic_period_name` varchar(50) NOT NULL, `institution_id` int(11) NOT NULL, `institution_code` varchar(50) NOT NULL, `total_grades` int(11) NOT NULL, `total_classes` int(11) NOT NULL, `total_lands` int(11) NOT NULL, `total_land_size` int(11) NOT NULL, `total_buildings` int(11) NOT NULL, `total_building_sizes` int(11) NOT NULL, `total_floors` int(11) NOT NULL, `total_floor_sizes` int(11) NOT NULL, `total_rooms` int(11) NOT NULL, `total_room_sizes` int(11) NOT NULL, `total_room_classrooms` int(11) NOT NULL, `total_room_classroom_sizes` int(11) NOT NULL, `total_students` int(11) NOT NULL, `total_students_female` int(11) NOT NULL, `total_students_male` int(11) NOT NULL, `total_staff_teaching` int(11) NOT NULL, `total_staff_teaching_female` int(11) NOT NULL, `total_staff_teaching_male` int(11) NOT NULL, `total_staff_non_teaching` int(11) NOT NULL, `total_staff_non_teaching_female` int(11) NOT NULL, `total_staff_non_teaching_male` int(11) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;');
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_institutions_truncate", "TRUNCATE summary_institutions;", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_institutions_insert", "INSERT INTO summary_institutions (academic_period_id, academic_period_name, institution_id, institution_code, total_grades, total_classes, total_lands, total_land_size, total_buildings, total_building_sizes, total_floors, total_floor_sizes, total_rooms, total_room_sizes, total_room_classrooms, total_room_classroom_sizes, total_students, total_students_female, total_students_male, total_staff_teaching, total_staff_teaching_female, total_staff_teaching_male, total_staff_non_teaching, total_staff_non_teaching_female, total_staff_non_teaching_male) SELECT academic_periods.id academic_period_id ,academic_periods.name academic_period_name ,institutions.id institution_id ,institutions.code institution_code ,IFNULL(grade_count.total_grades, 0) total_grades ,IFNULL(class_count.total_classes, 0) total_classes ,IFNULL(land_info.total_lands, 0) total_lands ,IFNULL(land_info.total_land_size, 0) total_land_size ,IFNULL(building_info.total_buildings, 0) total_buildings ,IFNULL(building_info.total_building_sizes, 0) total_building_sizes ,IFNULL(floor_info.total_floors, 0) total_floors ,IFNULL(floor_info.total_floor_sizes, 0) total_floor_sizes ,IFNULL(room_info.total_rooms, 0) total_rooms ,IFNULL(room_info.total_room_sizes, 0) total_room_sizes ,IFNULL(classroom_info.total_room_classrooms, 0) total_room_classrooms ,IFNULL(classroom_info.total_room_classroom_sizes, 0) total_room_classroom_sizes ,IFNULL(student_info.total_students, 0) total_students ,IFNULL(student_info.total_students_female, 0) total_students_female ,IFNULL(student_info.total_students_male, 0) total_students_male ,IFNULL(teaching_staff_info.total_staff_teaching, 0) total_staff_teaching ,IFNULL(teaching_staff_info.total_staff_teaching_female, 0) total_staff_teaching_female ,IFNULL(teaching_staff_info.total_staff_teaching_male, 0) total_staff_teaching_male ,IFNULL(non_teaching_staff_info.total_staff_non_teaching, 0) total_staff_non_teaching ,IFNULL(non_teaching_staff_info.total_staff_non_teaching_female, 0) total_staff_non_teaching_female ,IFNULL(non_teaching_staff_info.total_staff_non_teaching_male, 0) total_staff_non_teaching_male FROM institutions INNER JOIN academic_periods ON (((institutions.date_closed IS NOT NULL AND institutions.date_opened <= academic_periods.start_date AND institutions.date_closed >= academic_periods.start_date) OR (institutions.date_closed IS NOT NULL AND institutions.date_opened <= academic_periods.end_date AND institutions.date_closed >= academic_periods.end_date) OR (institutions.date_closed IS NOT NULL AND institutions.date_opened >= academic_periods.start_date AND institutions.date_closed <= academic_periods.end_date)) OR (institutions.date_closed IS NULL AND institutions.date_opened <= academic_periods.end_date)) LEFT JOIN ( SELECT academic_periods.id academic_period_id ,institution_grades.institution_id ,COUNT(DISTINCT(education_grades.id)) total_grades FROM institution_grades INNER JOIN education_grades ON education_grades.id = institution_grades.education_grade_id INNER JOIN institutions ON institutions.id = institution_grades.institution_id INNER JOIN education_programmes ON education_programmes.id = education_grades.education_programme_id INNER JOIN education_cycles ON education_cycles.id = education_programmes.education_cycle_id INNER JOIN education_levels ON education_levels.id = education_cycles.education_level_id INNER JOIN education_systems ON education_systems.id = education_levels.education_system_id INNER JOIN academic_periods ON academic_periods.id = education_systems.academic_period_id GROUP BY institution_grades.institution_id ,academic_periods.id) grade_count ON grade_count.institution_id = institutions.id AND grade_count.academic_period_id = academic_periods.id LEFT JOIN ( SELECT institution_classes.academic_period_id ,institution_classes.institution_id ,COUNT(DISTINCT(institution_classes.id)) total_classes FROM institution_classes GROUP BY institution_classes.institution_id ,institution_classes.academic_period_id ) class_count ON class_count.institution_id = institutions.id AND class_count.academic_period_id = academic_periods.id LEFT JOIN ( SELECT institution_lands.academic_period_id ,institution_lands.institution_id ,COUNT(DISTINCT(institution_lands.id)) total_lands ,SUM(IFNULL(institution_lands.area, 0)) total_land_size FROM institution_lands GROUP BY institution_lands.institution_id ,institution_lands.academic_period_id ) land_info ON land_info.institution_id = institutions.id AND land_info.academic_period_id = academic_periods.id LEFT JOIN ( SELECT institution_buildings.academic_period_id ,institution_buildings.institution_id ,COUNT(DISTINCT(institution_buildings.id)) total_buildings ,SUM(IFNULL(institution_buildings.area, 0)) total_building_sizes FROM institution_buildings GROUP BY institution_buildings.institution_id ,institution_buildings.academic_period_id ) building_info ON building_info.institution_id = institutions.id AND building_info.academic_period_id = academic_periods.id LEFT JOIN ( SELECT institution_floors.academic_period_id ,institution_floors.institution_id ,COUNT(DISTINCT(institution_floors.id)) total_floors ,SUM(IFNULL(institution_floors.area, 0)) total_floor_sizes FROM institution_floors GROUP BY institution_floors.institution_id ,institution_floors.academic_period_id ) floor_info ON floor_info.institution_id = institutions.id AND floor_info.academic_period_id = academic_periods.id LEFT JOIN ( SELECT institution_rooms.academic_period_id ,institution_rooms.institution_id ,COUNT(DISTINCT(institution_rooms.id)) total_rooms ,SUM(IFNULL(institution_rooms.area, 0)) total_room_sizes FROM institution_rooms GROUP BY institution_rooms.institution_id ,institution_rooms.academic_period_id ) room_info ON room_info.institution_id = institutions.id AND room_info.academic_period_id = academic_periods.id LEFT JOIN ( SELECT institution_rooms.academic_period_id ,institution_rooms.institution_id ,COUNT(DISTINCT(institution_rooms.id)) total_room_classrooms ,SUM(IFNULL(institution_rooms.area, 0)) total_room_classroom_sizes FROM institution_rooms INNER JOIN room_types ON room_types.id = institution_rooms.room_type_id WHERE room_types.classification = 1 GROUP BY institution_rooms.institution_id ,institution_rooms.academic_period_id ) classroom_info ON classroom_info.institution_id = institutions.id AND classroom_info.academic_period_id = academic_periods.id LEFT JOIN ( SELECT institution_students.academic_period_id ,institution_students.institution_id ,SUM(CASE WHEN security_users.gender_id IN (1, 2) THEN 1 ELSE 0 END) total_students ,SUM(CASE WHEN security_users.gender_id = 2 THEN 1 ELSE 0 END) total_students_female ,SUM(CASE WHEN security_users.gender_id = 1 THEN 1 ELSE 0 END) total_students_male FROM institution_students INNER JOIN security_users ON security_users.id = institution_students.student_id INNER JOIN academic_periods ON academic_periods.id = institution_students.academic_period_id WHERE IF((CURRENT_DATE >= academic_periods.start_date AND CURRENT_DATE <= academic_periods.end_date), institution_students.student_status_id = 1, institution_students.student_status_id IN (1, 7, 6, 8)) GROUP BY institution_students.institution_id ,institution_students.academic_period_id ) student_info ON student_info.institution_id = institutions.id AND student_info.academic_period_id = academic_periods.id LEFT JOIN ( SELECT academic_periods.id academic_period_id ,institution_staff.institution_id ,SUM(CASE WHEN security_users.gender_id IN (1, 2) THEN 1 ELSE 0 END) total_staff_teaching ,SUM(CASE WHEN security_users.gender_id = 2 THEN 1 ELSE 0 END) total_staff_teaching_female ,SUM(CASE WHEN security_users.gender_id = 1 THEN 1 ELSE 0 END) total_staff_teaching_male FROM institution_staff INNER JOIN security_users ON security_users.id = institution_staff.staff_id INNER JOIN institution_positions ON institution_positions.id = institution_staff.institution_position_id AND institution_positions.institution_id = institution_staff.institution_id INNER JOIN staff_position_titles ON staff_position_titles.id = institution_positions.staff_position_title_id INNER JOIN academic_periods ON (((institution_staff.end_date IS NOT NULL AND institution_staff.start_date <= academic_periods.start_date AND institution_staff.end_date >= academic_periods.start_date) OR (institution_staff.end_date IS NOT NULL AND institution_staff.start_date <= academic_periods.end_date AND institution_staff.end_date >= academic_periods.end_date) OR (institution_staff.end_date IS NOT NULL AND institution_staff.start_date >= academic_periods.start_date AND institution_staff.end_date <= academic_periods.end_date)) OR (institution_staff.end_date IS NULL AND institution_staff.start_date <= academic_periods.end_date)) WHERE institution_staff.staff_status_id = 1 AND staff_position_titles.type = 1 GROUP BY institution_staff.institution_id ,academic_periods.id ) teaching_staff_info ON teaching_staff_info.institution_id = institutions.id AND teaching_staff_info.academic_period_id = academic_periods.id LEFT JOIN ( SELECT academic_periods.id academic_period_id ,institution_staff.institution_id ,SUM(CASE WHEN security_users.gender_id IN (1, 2) THEN 1 ELSE 0 END) total_staff_non_teaching ,SUM(CASE WHEN security_users.gender_id = 2 THEN 1 ELSE 0 END) total_staff_non_teaching_female ,SUM(CASE WHEN security_users.gender_id = 1 THEN 1 ELSE 0 END) total_staff_non_teaching_male FROM institution_staff INNER JOIN security_users ON security_users.id = institution_staff.staff_id INNER JOIN institution_positions ON institution_positions.id = institution_staff.institution_position_id AND institution_positions.institution_id = institution_staff.institution_id INNER JOIN staff_position_titles ON staff_position_titles.id = institution_positions.staff_position_title_id INNER JOIN academic_periods ON (((institution_staff.end_date IS NOT NULL AND institution_staff.start_date <= academic_periods.start_date AND institution_staff.end_date >= academic_periods.start_date) OR (institution_staff.end_date IS NOT NULL AND institution_staff.start_date <= academic_periods.end_date AND institution_staff.end_date >= academic_periods.end_date) OR (institution_staff.end_date IS NOT NULL AND institution_staff.start_date >= academic_periods.start_date AND institution_staff.end_date <= academic_periods.end_date)) OR (institution_staff.end_date IS NULL AND institution_staff.start_date <= academic_periods.end_date)) WHERE institution_staff.staff_status_id = 1 AND staff_position_titles.type = 0 GROUP BY institution_staff.institution_id ,academic_periods.id ) non_teaching_staff_info ON non_teaching_staff_info.institution_id = institutions.id AND non_teaching_staff_info.academic_period_id = academic_periods.id;", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');
    

        $this->execute('CREATE TABLE IF NOT EXISTS `summary_institution_grades`( `academic_period_id` int(11) NOT NULL, `academic_period_name` varchar(50) NOT NULL, `institution_id` int(11) NOT NULL, `institution_code` varchar(50) NOT NULL, `grade_id` int(11) NOT NULL, `grade_name` varchar(50) NOT NULL, `total_classes` int(9) NOT NULL, `total_classes_female` int(9) NOT NULL, `total_classes_male` int(9) NOT NULL, `total_classes_mixed` int(9) NOT NULL, `total_students` int(9) NOT NULL, `total_students_female` int(9) NOT NULL, `total_students_male` int(9) NOT NULL, `total_home_room_teachers` int(9) NOT NULL, `total_secondary_teachers` int(9) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;');
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_institution_grades_truncate", "TRUNCATE summary_institution_grades;", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_institution_grades_insert", "INSERT INTO summary_institution_grades (academic_period_id, academic_period_name, institution_id, institution_code, grade_id, grade_name, total_classes, total_classes_female, total_classes_male, total_classes_mixed, total_students, total_students_female, total_students_male, total_home_room_teachers, total_secondary_teachers) SELECT academic_periods.id academic_period_id ,academic_periods.name academic_period_name ,institutions.id institution_id ,institutions.code institution_code ,education_grades.id grade_id ,education_grades.name grade_name ,IFNULL(SUM(classes_info.total_classes), 0) total_classes ,IFNULL(SUM(classes_info.female_classes), 0) total_classes_female ,IFNULL(SUM(classes_info.male_classes), 0) total_classes_male ,IFNULL(SUM(classes_info.mixed_classes), 0) total_classes_mixed ,IFNULL(SUM(student_info.total_students), 0) total_students ,IFNULL(SUM(student_info.total_students_female), 0) total_students_female ,IFNULL(SUM(student_info.total_students_male), 0) total_students_male ,IFNULL(SUM(classes_info.total_home_room_teachers), 0) total_home_room_teachers ,IFNULL(SUM(classes_info.total_secondary_teachers), 0) total_secondary_teachers FROM institution_grades INNER JOIN education_grades ON education_grades.id = institution_grades.education_grade_id INNER JOIN institutions ON institutions.id = institution_grades.institution_id INNER JOIN education_programmes ON education_programmes.id = education_grades.education_programme_id INNER JOIN education_cycles ON education_cycles.id = education_programmes.education_cycle_id INNER JOIN education_levels ON education_levels.id = education_cycles.education_level_id INNER JOIN education_systems ON education_systems.id = education_levels.education_system_id INNER JOIN academic_periods ON academic_periods.id = education_systems.academic_period_id LEFT JOIN ( SELECT academic_periods.id academic_period_id ,institution_classes.institution_id ,education_grades.id education_grade_id ,SUM(CASE WHEN institution_classes.total_male_students > 0 AND institution_classes.total_female_students = 0 THEN 1 ELSE 0 END) male_classes ,SUM(CASE WHEN institution_classes.total_male_students = 0 AND institution_classes.total_female_students > 0 THEN 1 ELSE 0 END) female_classes ,SUM(CASE WHEN IF((CURRENT_DATE >= academic_periods.start_date AND CURRENT_DATE <= academic_periods.end_date), (institution_classes.total_male_students > 0 AND institution_classes.total_female_students > 0), ((institution_classes.total_male_students > 0 AND institution_classes.total_female_students > 0) OR (institution_classes.total_male_students = 0 AND institution_classes.total_female_students = 0))) THEN 1 ELSE 0 END) mixed_classes ,COUNT(institution_classes.id) total_classes ,COUNT(DISTINCT(CASE WHEN institution_classes.staff_id != 0 THEN institution_classes.staff_id END)) total_home_room_teachers ,COUNT(DISTINCT(institution_classes_secondary_staff.secondary_staff_id)) total_secondary_teachers FROM institution_class_grades INNER JOIN institution_classes ON institution_classes.id = institution_class_grades.institution_class_id INNER JOIN education_grades ON education_grades.id = institution_class_grades.education_grade_id INNER JOIN academic_periods ON academic_periods.id = institution_classes.academic_period_id LEFT JOIN institution_classes_secondary_staff ON institution_classes_secondary_staff.institution_class_id = institution_classes.id GROUP BY academic_periods.id ,institution_classes.institution_id ,institution_class_grades.education_grade_id) classes_info ON classes_info.academic_period_id = academic_periods.id AND classes_info.institution_id = institutions.id AND classes_info.education_grade_id = education_grades.id LEFT JOIN ( SELECT institution_students.academic_period_id ,institution_students.institution_id ,institution_students.education_grade_id ,SUM(CASE WHEN security_users.gender_id IN (1,2) AND IF((CURRENT_DATE >= academic_periods.start_date AND CURRENT_DATE <= academic_periods.end_date), institution_students.student_status_id = 1, institution_students.student_status_id IN (1, 7, 6, 8)) THEN 1 ELSE 0 END) total_students ,SUM(CASE WHEN security_users.gender_id = 2 AND IF((CURRENT_DATE >= academic_periods.start_date AND CURRENT_DATE <= academic_periods.end_date), institution_students.student_status_id = 1, institution_students.student_status_id IN (1, 7, 6, 8)) THEN 1 ELSE 0 END) total_students_female ,SUM(CASE WHEN security_users.gender_id = 1 AND IF((CURRENT_DATE >= academic_periods.start_date AND CURRENT_DATE <= academic_periods.end_date), institution_students.student_status_id = 1, institution_students.student_status_id IN (1, 7, 6, 8)) THEN 1 ELSE 0 END) total_students_male FROM institution_students INNER JOIN security_users ON security_users.id = institution_students.student_id INNER JOIN academic_periods ON academic_periods.id = institution_students.academic_period_id GROUP BY institution_students.academic_period_id ,institution_students.institution_id ,institution_students.education_grade_id ) student_info ON student_info.academic_period_id = academic_periods.id AND student_info.institution_id = institutions.id AND student_info.education_grade_id = education_grades.id GROUP BY academic_periods.id ,institution_grades.institution_id ,education_grades.id;", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');
        

        $this->execute('CREATE TABLE IF NOT EXISTS `summary_institution_room_types`( `academic_period_id` int(11) NOT NULL, `academic_period_name` varchar(50) NOT NULL, `institution_id` int(11) NOT NULL, `institution_code` varchar(50) NOT NULL, `room_type` varchar(50) NOT NULL, `total_rooms` int(9) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;');
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_institution_room_types_truncate", "TRUNCATE summary_institution_room_types;", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_institution_room_types_insert", "INSERT INTO summary_institution_room_types (academic_period_id, academic_period_name, institution_id, institution_code, room_type, total_rooms) SELECT academic_periods.id academic_period_id ,academic_periods.name academic_period_name ,institutions.id institution_id ,institutions.code institution_code ,room_types.name room_type ,COUNT(DISTINCT(institution_rooms.id)) total_rooms FROM institution_rooms INNER JOIN room_types ON room_types.id = institution_rooms.room_type_id INNER JOIN institutions ON institutions.id = institution_rooms.institution_id INNER JOIN academic_periods ON academic_periods.id = institution_rooms.academic_period_id GROUP BY academic_periods.id ,institutions.id ,room_types.id;", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');
        

        $this->execute('CREATE TABLE IF NOT EXISTS `summary_institution_nationalities`( `academic_period_id` int(11) NOT NULL, `academic_period_name` varchar(50) NOT NULL, `institution_id` int(11) NOT NULL, `institution_code` varchar(50) NOT NULL, `nationality_id` int(11) NOT NULL, `nationality_name` varchar(50) NOT NULL, `total_students` int(9) NOT NULL, `total_students_female` int(9) NOT NULL, `total_students_male` int(9) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;');
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_institution_nationalities_truncate", "TRUNCATE summary_institution_nationalities;", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_institution_nationalities_insert", "INSERT INTO summary_institution_nationalities (academic_period_id, academic_period_name, institution_id, institution_code, nationality_id, nationality_name, total_students, total_students_female, total_students_male) SELECT academic_periods.id academic_period_id ,academic_periods.name academic_period_name ,institutions.id institution_id ,institutions.code institution_code ,IFNULL(nationalities.id, 0) nationality_id ,nationalities.name nationality_name ,SUM(CASE WHEN security_users.gender_id IN (1,2) AND IF((CURRENT_DATE >= academic_periods.start_date AND CURRENT_DATE <= academic_periods.end_date), institution_students.student_status_id = 1, institution_students.student_status_id IN (1, 7, 6, 8)) THEN 1 ELSE 0 END) total_students ,SUM(CASE WHEN security_users.gender_id = 2 AND IF((CURRENT_DATE >= academic_periods.start_date AND CURRENT_DATE <= academic_periods.end_date), institution_students.student_status_id = 1, institution_students.student_status_id IN (1, 7, 6, 8)) THEN 1 ELSE 0 END) total_students_female ,SUM(CASE WHEN security_users.gender_id = 1 AND IF((CURRENT_DATE >= academic_periods.start_date AND CURRENT_DATE <= academic_periods.end_date), institution_students.student_status_id = 1, institution_students.student_status_id IN (1, 7, 6, 8)) THEN 1 ELSE 0 END) total_students_male FROM institution_students INNER JOIN security_users ON security_users.id = institution_students.student_id INNER JOIN institutions ON institutions.id = institution_students.institution_id INNER JOIN academic_periods ON academic_periods.id = institution_students.academic_period_id LEFT JOIN user_nationalities ON user_nationalities.security_user_id = security_users.id LEFT JOIN nationalities ON nationalities.id = user_nationalities.nationality_id GROUP BY institution_students.academic_period_id ,institution_students.institution_id ,nationalities.id;", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');
        

        $this->execute('CREATE TABLE IF NOT EXISTS `summary_institution_grade_nationalities`( `academic_period_id` int(11) NOT NULL, `academic_period_name` varchar(50) NOT NULL, `institution_id` int(11) NOT NULL, `institution_code` varchar(50) NOT NULL, `grade_id` int(11) NOT NULL, `grade_name` varchar(50) NOT NULL, `nationality_id` int(11) NOT NULL, `nationality_name` varchar(50) NOT NULL, `total_students` int(9) NOT NULL, `total_students_female` int(9) NOT NULL, `total_students_male` int(9) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;');
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_institution_grade_nationalities_truncate", "TRUNCATE summary_institution_grade_nationalities;", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_institution_grade_nationalities_insert", "INSERT INTO summary_institution_grade_nationalities(academic_period_id, academic_period_name, institution_id, institution_code, grade_id, grade_name, nationality_id, nationality_name, total_students, total_students_female, total_students_male) SELECT academic_periods.id academic_period_id ,academic_periods.name academic_period_name ,institutions.id institution_id ,institutions.code institution_code ,education_grades.id grade_id ,education_grades.name grade_name ,IFNULL(nationalities.id, 0) nationality_id ,nationalities.name nationality_name ,SUM(CASE WHEN security_users.gender_id IN (1,2) AND IF((CURRENT_DATE >= academic_periods.start_date AND CURRENT_DATE <= academic_periods.end_date), institution_students.student_status_id = 1, institution_students.student_status_id IN (1, 7, 6, 8)) THEN 1 ELSE 0 END) total_students ,SUM(CASE WHEN security_users.gender_id = 2 AND IF((CURRENT_DATE >= academic_periods.start_date AND CURRENT_DATE <= academic_periods.end_date), institution_students.student_status_id = 1, institution_students.student_status_id IN (1, 7, 6, 8)) THEN 1 ELSE 0 END) total_students_female ,SUM(CASE WHEN security_users.gender_id = 1 AND IF((CURRENT_DATE >= academic_periods.start_date AND CURRENT_DATE <= academic_periods.end_date), institution_students.student_status_id = 1, institution_students.student_status_id IN (1, 7, 6, 8)) THEN 1 ELSE 0 END) total_students_male FROM institution_students INNER JOIN security_users ON security_users.id = institution_students.student_id INNER JOIN education_grades ON education_grades.id = institution_students.education_grade_id INNER JOIN institutions ON institutions.id = institution_students.institution_id INNER JOIN academic_periods ON academic_periods.id = institution_students.academic_period_id LEFT JOIN user_nationalities ON user_nationalities.security_user_id = security_users.id LEFT JOIN nationalities ON nationalities.id = user_nationalities.nationality_id GROUP BY institution_students.academic_period_id ,institution_students.institution_id ,education_grades.id ,nationalities.id;", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');

    }
         
    // rollback
    public function down()
    {
        
        // Restore table
        $this->execute('DROP TABLE IF EXISTS `report_queries`');
        $this->execute('RENAME TABLE `zz_7004_report_queries` TO `report_queries`');

        // Drop summary tables
        $this->execute('DROP TABLE IF EXISTS `summary_institutions`');
        $this->execute('DROP TABLE IF EXISTS `summary_institution_grades`');
        $this->execute('DROP TABLE IF EXISTS `summary_institution_room_types`');
        $this->execute('DROP TABLE IF EXISTS `summary_institution_nationalities`');
        $this->execute('DROP TABLE IF EXISTS `summary_institution_grade_nationalities`');

    }
}